ALTER proc sp_approve_order_lineItem
(
    @reason_code        varchar(3),
    @approved_status    int,
    @hold_group         varchar(50),
    @user_name          varchar(50),
    @lineItem_Id        int,
    @salesOrder_id      int,
    @schedule_date      datetime
 )
 AS
    declare @status varchar(15)
    declare @dbrevised_date datetime
    DECLARE @DB_SCHEDULE_DATE DATETIME

     if(@approved_status = 0)
        set @status = 'APPROVED'
     else
        set @status = 'HOLD'

    /* Update Sales_Hold_table*/
    UPDATE Sales_Hold_Table SET Hold_Reason_Code = @reason_code, Is_Approved = @approved_status, Hold_By_User = @user_name, Timestamp = CURRENT_TIMESTAMP WHERE LineItem_Id = @lineItem_id and Hold_By_Group = @hold_group

    /*
    if the user does not enter a schd date then we should check if there is already
 	schd date available in the system. If not then its usually null, if not null then update the system
 	*/
 	/* To save Scheduled Date even it is empty  */
 	if ( @schedule_date = ' ' )
 	begin
 		-- update cool schedule date for that lineitem_id
 		UPDATE Customer_Open_Order_LineItem  SET COOL_schedule_date = 'NULL' WHERE LineItem_Id = @lineItem_id
 		UPDATE Customer_Change_Order_LineItem SET CCO_Schedule_Date = NULL
 		WHERE ChangeItem_ID = ( select ChangeItem_ID from Customer_Change_Order_LineItem where changeNo = (select max(changeNo) from Customer_Change_Order_LineItem where LineItem_Id = @lineItem_Id) and LineItem_Id = @lineItem_Id )
 	end
    else
 	begin
 	 	SELECT @DB_SCHEDULE_DATE = COOL_SCHEDULE_DATE, @dbrevised_date = COOL_Revised_Date FROM Customer_Open_Order_LineItem WHERE LineItem_Id = @lineItem_id
		IF ( ISNULL( @DB_SCHEDULE_DATE,'') <> ISNULL( @schedule_date,'' ) ) --SCHEDULE DATE IS MODIFIED
		 BEGIN
			IF ( @dbrevised_date IS NOT NULL OR LTRIM(RTRIM(@dbrevised_date)) != '' ) --REVISED DATE IS PRESENT HENCE WE NEED TO UPDATE THE REVISED DATE
			 BEGIN
				UPDATE Customer_Open_Order_LineItem  SET COOL_Revised_Date = @schedule_date
	 			WHERE LineItem_Id = @lineItem_id
				UPDATE Customer_Change_Order_LineItem SET CCO_Revised_Date = @schedule_date
	 			WHERE ChangeItem_ID = ( select ChangeItem_ID from Customer_Change_Order_LineItem where changeNo = (select max(changeNo) from Customer_Change_Order_LineItem where LineItem_Id = @lineItem_id) and LineItem_Id = @lineItem_Id ) 
			 END
			ELSE --UPDATE SCHEDULE DATE
			 BEGIN
				UPDATE Customer_Open_Order_LineItem  SET COOL_schedule_date = @schedule_date
		 		WHERE LineItem_Id = @lineItem_id 
		 		UPDATE Customer_Change_Order_LineItem SET CCO_Schedule_Date = @schedule_date
		 		WHERE ChangeItem_ID = ( select ChangeItem_ID from Customer_Change_Order_LineItem where changeNo = (select max(changeNo) from Customer_Change_Order_LineItem where LineItem_Id = @lineItem_id) and LineItem_Id = @lineItem_Id )
			 END
		 END
     end

    declare @is_lineItem_approved_by_all int
    select @is_lineItem_approved_by_all = ( SELECT count(*) FROM Sales_Hold_Table WHERE LineItem_ID = @lineItem_id and Is_Approved = 1)
    /* execute the cancel order proc if it is already cancelled(this is approved by all) */
    declare @is_cancelled_lineItem varchar(50)
    declare @line_item_no int
    declare @process_step_id int
    select @line_item_no = COOL_Line_Item_No from Customer_Open_Order_LineItem where LineItem_ID = @lineItem_id
    select @process_step_id = ProcessStep_ID from Product where Product_ID = (select Product_ID from Customer_Open_Order_LineItem where LineItem_ID = @lineItem_id)

    if( @is_lineItem_approved_by_all = 0 )
    begin
        select @is_cancelled_lineItem = (SELECT LineItem_ID FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_ID and COOL_Is_Cancel = 0)

        if(@is_cancelled_lineItem is not null)
        begin
            exec cancel_order_proc @lineItem_id, @salesOrder_id, @user_name, @user_name
            exec sp_release_reserved_lots @line_item_no, @salesOrder_id, @process_step_id
        end
    end

    /* Update the lineItem STATUS */
    declare  @lineitem_status varchar(20)

    SELECT @lineitem_status =  COOL_Status FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_id
    if (@is_lineItem_approved_by_all = 0)
    begin
        if(@lineitem_status <> 'SHIPPED')
        begin
 	        UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'APPROVED', TimeStamp = CURRENT_TIMESTAMP WHERE LineItem_ID = @lineItem_id
        end
    end
    else
    begin
 	    UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'HOLD', Timestamp = CURRENT_TIMESTAMP WHERE LineItem_ID = @lineItem_id
    end

    /* Check if the all lineItems in the order is scheduled by all the operations */
    declare @is_order_approved int 
    /* Check if the order is approved by all Approvers*/
    select @is_order_approved = ( SELECT count(*) FROM Sales_Hold_Table WHERE SalesOrder_ID = @salesOrder_id and Is_Approved = 1)
    /* if count = 0 then it is approved by all approvers */
    if ( @is_order_approved = 0)
    begin
 	    /* Update the sales_order table for is_pending */
 	    UPDATE Sales_Order SET SO_Is_Pending = 1, SO_Status = 'APPROVED', TimeStamp = CURRENT_TIMESTAMP WHERE SalesOrder_ID = @salesOrder_id
    end
    else
    begin
        UPDATE Sales_Order SET SO_Is_Pending = 0, SO_Status = 'HOLD',TimeStamp = CURRENT_TIMESTAMP WHERE SalesOrder_ID = @salesOrder_id
    end
 
    declare @count_txable_line_items int
    select @count_txable_line_items = ( SELECT count(*) from Customer_Open_Order_LineItem WHERE SalesOrder_ID = @salesOrder_id and COOL_Taxable = 0 )
    if( @count_txable_line_items = 0 )
    begin
	    UPDATE Sales_Order SET SO_Sales_Tax_Type = 0, So_Sales_Tax = 0.0 WHERE SalesOrder_ID = @salesOrder_id
    end